package com.vogue.supplymall.shop.domain.provider;

import com.vogue.ssm.database.util.DealStatus;
import com.vogue.supplymall.common.domain.dto.SearchInfo;

/**
 * The type Product unit sql provider.
 *
 * @aouthor: zhangliang
 * @date: Create in 2018-01-12
 */
public class ProductUnitSqlProvider {

    /**
     * Find string.
     * 商品详情-单位检索sql
     *
     * @param searchInfo the search info
     * @return the string
     */
    public String find(SearchInfo searchInfo) {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT ")
                .append(" pa.id, ")
                .append(" p.`name` pname, ")
                .append(" pp1.price public_price, ")
                .append(" pp2.price private_price, ")
                .append(" pa.inventory, ")
                .append(" pa.weight, ")
                .append(" p.hiddenflag,pa.defaultflag, ")
                .append(" pav.`name` unit_name ");
        sql.append(" FROM ")
                .append(" product p ")
                /*增加商品表关联-排除关闭的店铺*/
                .append(" INNER JOIN shop s ON (p.shopid = s.id and s.stopflag = 0)")
                .append(" INNER JOIN product_attrplan pa ON (p.id = pa.productid and pa.disabledflag = 0 ) ")
                .append(" INNER JOIN product_attrplan_detail pad ON (pa.id = pad.attrplanid) ")
                .append(" INNER JOIN product_attrvalue pav ON (pav.id = pad.attrvalueid) ")
                .append(" LEFT JOIN product_price pp1 ON ( ")
                .append(" p.id = pp1.productid ")
                .append(" AND pa.id = pp1.attrplanid ")
                .append(" AND pp1.allflag = 1 ")
                .append(" ) ")
                .append(" LEFT JOIN product_price pp2 ON ( ")
                .append(" p.id = pp2.productid ")
                .append(" AND pa.id = pp2.attrplanid ")
                .append(" AND pp2.allflag = 0 ");
        if (null == searchInfo.getSyscodeid()) {
            sql.append(" AND pp2.oneid = -1 ");
        } else {
            sql.append(" AND pp2.oneid = #{syscodeid} ")
                    // 2018/2/26 匹配时间区间允许的私有价格
                    .append(" AND pp2.begindate < NOW() ")
                    .append(" AND pp2.enddate > NOW() ");
        }
        sql.append(" ) ");
        sql.append(" WHERE ")
                .append(" p.id = #{id} ")
                /*增加商品表关联-排除关闭的店铺*/
                .append(" and s.disabledflag = 0 ")
                .append(" AND pa.disabledflag = 0 ");
        return DealStatus.formatShadeSql(sql.toString());
    }
}
